CREATE DATABASE StoreNetwork
GO
USE StoreNetwork
GO

CREATE TABLE Administrators(
	Username VARCHAR(32) PRIMARY KEY,
	[Password] VARCHAR(32) NOT NULL,
	FullName NVARCHAR(60),
	Email NVARCHAR(70),
	Phone VARCHAR(20),
	[View] BIT NOT NULL DEFAULT '1', -- 1 Allow View, 0 Not Allow View
	[Edit] BIT NOT NULL DEFAULT '1',
	[Delete] BIT NOT NULL DEFAULT '1',
	Acting TINYINT NOT NULL DEFAULT '0' -- 0 Super Admin, 1 Normal Admin, 2 Mod
)
GO

CREATE TABLE Accounts(
	AccountID INT IDENTITY(1, 1) PRIMARY KEY,
	Email VARCHAR(70) NOT NULL UNIQUE,
	[Password] VARCHAR(32) NOT NULL DEFAULT '123456',
	FacebookID VARCHAR(32), -- Khong trung nhung ngoai tru NULL
	AccessToken VARCHAR(200),	
	DateCreate DATETIME NOT NULL,
	[Type] TINYINT NOT NULL DEFAULT '0' -- 0 Normal User, 1 Admin Store
)
GO
CREATE UNIQUE INDEX UQ_Accounts_FacebookID ON Accounts (FacebookID) WHERE FacebookID IS NOT NULL;
GO

CREATE TABLE Stores(
	AccountID INT PRIMARY KEY,
	StoreName NVARCHAR(60) NOT NULL, --display within page
	Domain VARCHAR(50) NOT NULL UNIQUE,  -- cuahangmang.com/domain
	[Address] NVARCHAR(60),
	Phone VARCHAR(20),
	DateCreate DATETIME NOT NULL,
	[Views] INT NOT NULL DEFAULT '0', -- Luot truy cap cua cua hang
	Descript NVARCHAR(3000),
	Activate BIT NOT NULL DEFAULT '1' -- TINH TRANG KICH HOAT CUA CUA HANG, 1 DA KICH HOAT, 0 DA KHOA
)
GO

CREATE TABLE Profiles(
	AccountID INT PRIMARY KEY,
	FullName NVARCHAR(60),
	[Address] NVARCHAR(60),
	Phone VARCHAR(20),
	Birthday DATETIME
)
GO


--Bao xau ve cua hang
CREATE TABLE StoreWarnings(
	SWarningID INT IDENTITY(1, 1) PRIMARY KEY,
	AccountID INT NOT NULL,
	DateCreate DATETIME NOT NULL,
	Content NVARCHAR(300)
)
GO

CREATE TABLE Categorys(
	CategoryID INT IDENTITY(1, 1) PRIMARY KEY,
	AccountID INT NOT NULL,
	Name NVARCHAR(60) NOT NULL,
	ParentID INT NOT NULL,
	DisplayOrder INT -- Thu tu sap xep
)
GO

CREATE TABLE Products(
	ProductID INT IDENTITY(1, 1) PRIMARY KEY,
	CategoryID INT NOT NULL,
	Name NVARCHAR(60) NOT NULL,
	Price FLOAT,
	Currency NVARCHAR(20),
	Discount INT NOT NULL DEFAULT '0', --Giam gia cua san pham(don vi %)
	Warranty INT, --Don vi tinh la "Thang"
	ProviderName NVARCHAR(20),
	ThumbImg NVARCHAR(200) NOT NULL,
	[Views] INT NOT NULL DEFAULT 0, -- Luot xem
	Buys INT NOT NULL DEFAULT 0, -- Luot mua
	DateCreate DATETIME NOT NULL
)
GO

CREATE TABLE Images(
	ImageID INT IDENTITY(1, 1) PRIMARY KEY,
	ProductID INT NOT NULL,
	URL NVARCHAR(200) NOT NULL
)
GO

CREATE TABLE ProductCustomFields(
	PFieldID INT PRIMARY KEY,
	ProductID INT NOT NULL,
	[FieldName] NVARCHAR(50) NOT NULL,
	Value NVARCHAR(MAX)
)
GO

CREATE TABLE ProductRatings(
	PRatingID INT IDENTITY(1, 1) PRIMARY KEY,
	ProductID INT NOT NULL,
	Rating INT NOT NULL DEFAULT '0'
)
GO

CREATE TABLE ProductsBuy(
	ProductBuyID INT IDENTITY(1, 1) PRIMARY KEY,
	AccountID INT NOT NULL,
	CategoryName NVARCHAR(60) NOT NULL,
	Name NVARCHAR(60) NOT NULL,
	Price FLOAT,
	Currency NVARCHAR(20),
	Discount INT NOT NULL DEFAULT '0', --Giam gia cua san pham(don vi %)
	Warranty INT, --Don vi tinh la "Thang"
	ProviderName NVARCHAR(20),
	ThumbImg NVARCHAR(200) NOT NULL
)
GO

CREATE TABLE Bills(
	BillID INT IDENTITY(1, 1) PRIMARY KEY,
	AccountID INT NOT NULL,
	UserBuyID INT, -- Co the co hoac khong, hoa don cua ai
	FullName NVARCHAR(60) NOT NULL, -- Ten nguowi mua
	[Address] NVARCHAR(60), -- Dia chi nguoi mua
	Phone VARCHAR(20), -- So dien thoai nguoi mua
	Email VARCHAR(70), -- Email thoai nguoi mua
	DateCreate DATETIME NOT NULL,
	[Status] BIT NOT NULL DEFAULT '0', -- TRANG THAI THANH TOAN, 0 CHUA THANH TOAN, 1 DA THANH TOAN
)
GO

CREATE TABLE Deliverys(
	DeliveryID INT IDENTITY(1, 1) PRIMARY KEY,
	BillID INT NOT NULL,
	FullName NVARCHAR(60), -- Ten nguoi nhan
	[Address] NVARCHAR(60) NOT NULL, -- Dia chi nguoi nhan
	Phone VARCHAR(20), -- So dien thoai nguoi nhan
)
GO

CREATE TABLE DeliveryDetails(
	DelivDetailID INT IDENTITY(1, 1) PRIMARY KEY,
	DeliveryID INT NOT NULL,
	ProductBuyID INT NOT NULL,
	Quanity INT NOT NULL DEFAULT '0',
	QuanUnit NVARCHAR(50)
)
GO


--LIEN KET

ALTER TABLE Stores ADD CONSTRAINT FK_Stores_Accounts FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID) ON DELETE CASCADE;
ALTER TABLE Profiles ADD CONSTRAINT FK_Profiles_Accounts FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID) ON DELETE CASCADE;

ALTER TABLE Categorys ADD CONSTRAINT FK_Categorys_Categorys FOREIGN KEY (ParentID) REFERENCES Categorys(CategoryID);
ALTER TABLE Categorys ADD CONSTRAINT FK_Categorys_Stores FOREIGN KEY (AccountID) REFERENCES Stores(AccountID) ON DELETE CASCADE;
ALTER TABLE Products ADD CONSTRAINT FK_Products_Categorys FOREIGN KEY (CategoryID) REFERENCES Categorys(CategoryID) ON DELETE CASCADE;
ALTER TABLE ProductCustomFields ADD CONSTRAINT FK_ProductCustomFields_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE;
ALTER TABLE ProductRatings ADD CONSTRAINT FK_ProductRatings_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE;
ALTER TABLE Images ADD CONSTRAINT FK_Images_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE;

ALTER TABLE DeliveryDetails ADD CONSTRAINT FK_DeliveryDetails_Deliverys FOREIGN KEY (DeliveryID) REFERENCES Deliverys(DeliveryID) ON DELETE CASCADE;
ALTER TABLE DeliveryDetails ADD CONSTRAINT FK_DeliveryDetails_ProductsBuy FOREIGN KEY (ProductBuyID) REFERENCES ProductsBuy(ProductBuyID);
ALTER TABLE Deliverys ADD CONSTRAINT FK_Deliverys_Bills FOREIGN KEY (BillID) REFERENCES Bills(BillID) ON DELETE CASCADE;

ALTER TABLE Bills ADD CONSTRAINT FK_Bills_Stores FOREIGN KEY (AccountID) REFERENCES Stores(AccountID) ON DELETE CASCADE;
ALTER TABLE Bills ADD CONSTRAINT FK_Bills_Profiles FOREIGN KEY (UserBuyID) REFERENCES Profiles(AccountID);
ALTER TABLE ProductsBuy ADD CONSTRAINT FK_ProductsBuy_Stores FOREIGN KEY (AccountID) REFERENCES Stores(AccountID) ON DELETE CASCADE;

ALTER TABLE StoreWarnings ADD CONSTRAINT FK_StoreWarnings_Stores FOREIGN KEY (AccountID) REFERENCES Stores(AccountID) ON DELETE CASCADE;